<HTML>

<HEAD><TITLE>Log Parser sample queries</TITLE></HEAD>

<BODY BGCOLOR="#EFEFFF" LINK="#000000" VLINK="#606060" ALINK="#303030">

<FONT SIZE="5"><B>IIS log files queries</B></FONT><BR>

<P>
These examples assume the log format used is <TT>W3C Extended</TT>. If a different format is used, the field names may need to be changed.
</P>

<P>
<FONT SIZE="3"><B>
<A HREF="RequestsPerHour.sql">
Get the number of requests and total bytes sent during each hour
</A><BR>
</B></FONT>

<TT><PRE>
SELECT	QUANTIZE(TO_TIMESTAMP(date, time), 3600) AS Hour, 
	COUNT(*) AS Total,  
	SUM(sc-bytes) AS TotBytesSent 
FROM ex*.log 
GROUP BY Hour 
ORDER BY Hour
</PRE></TT>
</P>




<P>
<FONT SIZE="3"><B>
<A HREF="ExtensionByte.sql">
Create a pie chart with the total number of bytes generated by each extension
</A><BR>
</B></FONT>
<FONT SIZE="2">To use: &quot;LogParser file:ExtensionByte.sql -charttype:PieExploded -charttitle:&quot;Bytes per extension&quot; -categories:off&quot;</FONT>

<TT><PRE>
SELECT	EXTRACT_EXTENSION( cs-uri-stem ) AS Extension,
	MUL(PROPSUM(sc-bytes),100.0) AS Bytes
INTO Pie.gif
FROM &lt;1&gt;
GROUP BY Extension
ORDER BY Bytes DESC
</PRE></TT>
</P>



<P>
<FONT SIZE="3"><B>
<A HREF="TOP20Verbs.sql">
Get the Top 20 verbs with the maximum and average time taken, and with the average number of bytes sent
</A><BR>
</B></FONT>

<TT><PRE>
SELECT TOP 20 	cs-method, 
		COUNT(*) AS Total, 
		MAX(time-taken) AS MaxTime, 
		AVG(time-taken) AS AvgTime, 
		AVG(sc-bytes) AS AvgBytesSent 
FROM ex*.log 
GROUP BY cs-method 
ORDER BY Total DESC
</PRE></TT>
</P>




<P>
<FONT SIZE="3"><B>
<A HREF="TOP20URIs.sql">
Get the Top 20 URIs with the maximum and average time taken, and with the average number of bytes sent
</A><BR>
</B></FONT>

<TT><PRE>
SELECT TOP 20 	cs-uri-stem, 
		COUNT(*) AS Total, 
		MAX(time-taken) AS MaxTime, 
		AVG(time-taken) AS AvgTime, 
		AVG(sc-bytes) AS AvgBytesSent 
FROM ex*.log 
GROUP BY cs-uri-stem
ORDER BY Total DESC
</PRE></TT>
</P>




<P>
<FONT SIZE="3"><B>
<A HREF="StatusSubStatusCount.sql">
Get the full HTTP status codes by number of hits
</A><BR>
</B></FONT>

<TT><PRE>
SELECT 	STRCAT(TO_STRING(sc-status), STRCAT('.', TO_STRING(sc-substatus))) AS Status, 
	COUNT(*) AS Total 
FROM ex*.log 
GROUP BY Status 
ORDER BY Total DESC
</PRE></TT>
</P>



<P>
<FONT SIZE="3"><B>
<A HREF="ASPErrors.sql">
For each ASP error, get the Url and the ASP script line number
</A><BR>
</B></FONT>

<TT><PRE>
SELECT  EXTRACT_TOKEN(FullUri, 0, '|') AS Uri,
        EXTRACT_TOKEN(cs-uri-query, -1, '|') AS ErrorMsg,
        EXTRACT_TOKEN(cs-uri-query, 1, '|') AS LineNo,
        COUNT(*) AS Total 
USING   STRCAT( cs-uri-stem,
                REPLACE_IF_NOT_NULL(cs-uri-query, STRCAT('?', cs-uri-query))
        ) AS FullUri
FROM ex*.log 
WHERE (sc-status = 500) AND (cs-uri-stem LIKE '%.asp') 
GROUP BY Uri, ErrorMsg, LineNo
ORDER BY Total DESC
</PRE></TT>
</P>





<P>
<FONT SIZE="3"><B>
<A HREF="Errors.sql">
Get requests and full status by number of hits
</A><BR>
</B></FONT>

<TT><PRE>
SELECT 	STRCAT(	cs-uri-stem, 
		REPLACE_IF_NOT_NULL(cs-uri-query, STRCAT('?',cs-uri-query))
		) AS Request, 
	STRCAT(	TO_STRING(sc-status), 		
		STRCAT(	'.',
			COALESCE(TO_STRING(sc-substatus), '?' )
			)
		) AS Status, 
	COUNT(*) AS Total 
FROM ex*.log 
WHERE (sc-status >= 400) 
GROUP BY Request, Status 
ORDER BY Total DESC
</PRE></TT>
</P>






<P>
<FONT SIZE="3"><B>
<A HREF="Extensions.sql">
Get hit counts for each extension
</A><BR>
</B></FONT>

<TT><PRE>
SELECT  EXTRACT_EXTENSION( cs-uri-stem ) AS Extension, 
	COUNT(*) AS Total 
FROM ex*.log 
GROUP BY Extension 
ORDER BY Total DESC
</PRE></TT>
</P>






<P>
<FONT SIZE="3"><B>
<A HREF="AuthFailures.sql">
Get authentication and authorization failures
</A><BR>
</B></FONT>

<TT><PRE>
SELECT 	cs-username, 
	sc-status, 
	COUNT(*) AS Total 
FROM ex*.log 
WHERE cs-username IS NOT NULL AND sc-status BETWEEN 401 AND 403
GROUP BY cs-username,sc-status 
ORDER BY Total DESC
</PRE></TT>
</P>









<P><BR></P>

<FONT SIZE="5"><B>Event log queries</B></FONT><BR>

<P>
<FONT SIZE="3"><B>
<A HREF="LogonFailures.sql">
Get logon failures from the Security Event Log
</A><BR>
</B></FONT>

<TT><PRE>
SELECT 	STRCAT(	EXTRACT_TOKEN(	Strings,
				1,
				'|'),
		STRCAT(	'\\',
			EXTRACT_TOKEN(	Strings,
					0,
					'|'
					)
			)
		) AS User,
		COUNT(*) AS Total 
FROM Security 
WHERE EventType = 16 AND EventCategory = 2 
GROUP BY User 
ORDER BY Total DESC
</PRE></TT>
</P>



<P>
<FONT SIZE="3"><B>
<A HREF="LogonFailureStats.sql">
Get logon failure statistics from the Security Event Log
</A><BR>
</B></FONT>
<FONT SIZE="2">To use: &quot;LogParser file:LogonFailureStats.sql?machine=MyMachineName&quot;</FONT>


<TT><PRE>
SELECT
	COUNT(EventID) AS TotalLogonFailures,
	TO_LOWERCASE(EXTRACT_TOKEN(Strings,0,'|')) AS User,
	TO_LOWERCASE(EXTRACT_TOKEN(Strings,1,'|')) AS Domain,
	TO_LOWERCASE(EXTRACT_TOKEN(Strings,5,'|')) AS WorkStation,
	CASE TO_INT(EXTRACT_TOKEN(Strings,2,'|')) 
		WHEN 2 THEN  'Interactive - Intended for users who will be interactively using the machine, such as a user being logged on by a terminal server, remote shell, or similar process.'
		WHEN 3 THEN  'Network - Intended for high performance servers to authenticate clear text passwords. LogonUser does not cache credentials for this logon type.'
		WHEN 4 THEN  'Batch - Intended for batch servers, where processes may be executing on behalf of a user without their direct intervention; or for higher performance servers that process many clear-text authentication attempts at a time, such as mail or web servers. LogonUser does not cache credentials for this logon type.'
		WHEN 5 THEN  'Service - Indicates a service-type logon. The account provided must have the service privilege enabled.'
		WHEN 6 THEN  'Proxy - Indicates a proxy-type logon.'
		WHEN 7 THEN  'Unlock - This logon type is intended for GINA DLLs logging on users who will be interactively using the machine. This logon type allows a unique audit record to be generated that shows when the workstation was unlocked.'
		WHEN 8 THEN  'NetworkCleartext - Windows 2000; Windows XP and Windows Server 2003 family:  Preserves the name and password in the authentication packages, allowing the server to make connections to other network servers while impersonating the client. This allows a server to accept clear text credentials from a client, call LogonUser, verify that the user can access the system across the network, and still communicate with other servers.'
		WHEN 9 THEN  'NewCredentials - Windows 2000; Windows XP and Windows Server 2003 family:  Allows the caller to clone its current token and specify new credentials for outbound connections. The new logon session has the same local identity, but uses different credentials for other network connections.'
		WHEN 10 THEN 'RemoteInteractive - Terminal Server session that is both remote and interactive.'
		WHEN 11 THEN 'CachedInteractive - Attempt cached credentials without accessing the network.'
		WHEN 12 THEN 'CachedRemoteInteractive - Same as RemoteInteractive. This is used for internal auditing.'
		WHEN 13 THEN 'CachedUnlock - Workstation logon'
		ELSE EXTRACT_TOKEN(Strings,2,'|')
	END AS Type
INTO DATAGRID
FROM \\%machine%\security
WHERE EventID IN (529)
GROUP BY User,Domain,WorkStation,Type
ORDER BY TotalLogonFailures DESC
</PRE></TT>
</P>



<P>
<FONT SIZE="3"><B>
<A HREF="LogonSuccesses.sql">
Get logon successes from the Security Event Log
</A><BR>
</B></FONT>

<TT><PRE>
SELECT 	STRCAT(	EXTRACT_TOKEN(	Strings,
				1,
				'|'),
		STRCAT(	'\\',
			EXTRACT_TOKEN(	Strings,
					0,
					'|'
					)
			)
		) AS User,
		COUNT(*) AS Total 
FROM Security 
WHERE EventType = 8 AND EventCategory = 2 
GROUP BY User 
ORDER BY Total DESC
</PRE></TT>
</P>






<P>
<FONT SIZE="3"><B>
<A HREF="EventIDDistrib.sql">
Get the distribution of EventID values for each Source
</A><BR>
</B></FONT>

<TT><PRE>
SELECT	SourceName, 
	EventID, 
	MUL(PROPCOUNT(*) ON (SourceName), 100.0) AS Percent
FROM System
GROUP BY SourceName, EventID
ORDER BY SourceName, Percent DESC
</PRE></TT>
</P>




<P><BR></P>

<FONT SIZE="5"><B>UrlScan queries</B></FONT><BR>

<P>
<FONT SIZE="3"><B>
<A HREF="ClientUrls.sql">
Get the clients whose requests have been rejected by UrlScan
</A><BR>
</B></FONT>

<TT><PRE>
SELECT	ClientIP,
	COUNT(*)
FROM URLSCAN
WHERE Comment LIKE 'Url%'
GROUP BY ClientIP
</PRE></TT>
</P>


<P>
<FONT SIZE="3"><B>
<A HREF="UrlScanComments.sql">
Get all the UrlScan comments
</A><BR>
</B></FONT>

<TT><PRE>
SELECT DISTINCT Comment
FROM URLSCAN
</PRE></TT>
</P>






<P><BR></P>

<FONT SIZE="5"><B>ETW log queries</B></FONT><BR>

<P>
<FONT SIZE="3"><B>
<A HREF="AppPools.sql">
Get the number of hits for each IIS AppPool
</A><BR>
</B></FONT>

<FONT SIZE="2">To use: &quot;LogParser file:AppPools.sql -fmode:full -providers:"HTTP Service Trace"&quot;</FONT>

<TT><PRE>
SELECT 	AppPoolName,
	COUNT(*) AS Total
FROM *.etl
WHERE EventTypeName = 'Deliver'
GROUP BY AppPoolName
ORDER BY Total DESC
</PRE></TT>
</P>




<P><BR></P>

<FONT SIZE="5"><B>ADS queries</B></FONT><BR>

<P>
<FONT SIZE="3"><B>
<A HREF="VirtualDirWrite.sql">
Get the IIS Virtual Directories that allow WRITE access
</A><BR>
</B></FONT>

<FONT SIZE="2">To use: &quot;LogParser file:VirtualDirWrite.sql -i:ADS -class:IIsWebVirtualDir&quot;</FONT>

<TT><PRE>
SELECT ObjectPath
FROM IIS://localhost/W3SVC
WHERE BIT_AND(AccessFlags, 0x02) <> 0
</PRE></TT>
</P>




<P><BR></P>

<FONT SIZE="5"><B>NETMON queries</B></FONT><BR>

<P>
<FONT SIZE="3"><B>
<A HREF="BytesPerSec.sql">
Get the total network bytes per second
</A><BR>
</B></FONT>

<TT><PRE>
SELECT 	QUANTIZE(DateTime, 1) AS Second, 
	SUM(FrameBytes)
FROM myCapture.cap
GROUP BY Second
</PRE></TT>
</P>



<P><BR></P>

<FONT SIZE="5"><B>Generic queries</B></FONT><BR>

<P>
<FONT SIZE="3"><B>
<A HREF="Words.sql">
TEXTWORD Input: Get a list of all the words in these text files ordered by number of times they appear
</A><BR>
</B></FONT>
<FONT SIZE="2">To use: &quot;LogParser file:Words.sql -i:TEXTWORD&quot;</FONT>

<TT><PRE>
SELECT	Text,
	COUNT(*) AS Total
FROM C:\*.txt
GROUP BY Text
ORDER BY Total DESC
</PRE></TT>
</P>



<P>
<FONT SIZE="3"><B>
<A HREF="HiddenFiles.sql">
FS Input: Get a list of all the hidden files in the C: drive ordered by their size
</A><BR>
</B></FONT>
<FONT SIZE="2">To use: &quot;LogParser file:HiddenFiles.sql -i:FS&quot;</FONT>

<TT><PRE>
SELECT 	Path,
	Size
FROM C:\*.*
WHERE NOT Attributes LIKE '%D%' AND Attributes LIKE '%H%'
ORDER BY Size DESC
</PRE></TT>
</P>



<P>
<FONT SIZE="3"><B>
<A HREF="FilenameLengths.sql">
FS Input: Get the distribution of file name lengths in the C: drive
</A><BR>
</B></FONT>
<FONT SIZE="2">To use: &quot;LogParser file:FilenameLengths.sql -i:FS&quot;</FONT>

<TT><PRE>
SELECT 	STRLEN(Name) AS NameLen,
	COUNT(*) AS Total
FROM C:\*.*
GROUP BY NameLen
ORDER BY Total
</PRE></TT>
</P>


	

<P><BR></P>









<P><BR></P>

<FONT SIZE="5"><B>Helper functions</B></FONT><BR>


<BR>

<P>
<DL>
<DT>
<FONT SIZE="3"><B>
Get the status code in the &quot;status.substatus&quot; form:
</A><BR>
</B></FONT>
</DT>
<DD>
<TT><PRE>
STRCAT(TO_STRING(sc-status), STRCAT('.', TO_STRING(sc-substatus))) AS Status
</TT></PRE>
</DD>
</DL>
</P>


<P>
<DL>
<DT>
<FONT SIZE="3"><B>
Get the request in the &quot;uri-stem?uri-query&quot; form:
</A><BR>
</B></FONT>
</DT>
<DD>
<TT><PRE>
STRCAT(cs-uri-stem, REPLACE_IF_NOT_NULL(cs-uri-query,STRCAT('?',cs-uri-query))) AS Request
</TT></PRE>
</DD>
</DL>
</P>

	


</BODY>
</HTML>